In my previous article, I created a story to teach basic vocabulary about data. I thought it was much more interesting than a list of definitions with no context. In the story, I briefly mentioned an idea that deserves more attention: in Legal and Compliance, like in any department, data rarely exists in isolation. Most of the information stored by a team is connected to other pieces of information somewhere else. That remains true even when teams work in separate silos, use different files, or maintain different registers.

Yet it is often overlooked. A person may create a register without thinking about how it relates to existing records elsewhere in the department, or even to records they already maintain themselves. The result is not just duplication. It also creates unnecessary work, because the same information must be entered, checked, and updated more than once. Over time, that almost always leads to inconsistencies.

This problem usually becomes visible when data needs to be combined. For example, you may want to pull information from another register, enrich an existing record, or produce a consolidated report. That is when you realise there is no shared identifier, no reliable link between records, or that key data is missing altogether.

What is the root issue?

I believe there are several factors, but the main ones are education, training, and governance. Teams and people build and maintain their data in isolation without any global strategy.

If I had one piece of advice as a starting point, it would be as follows:

All data is interconnected.

Once you start from that principle, you will think about your data differently, and you will start storing it accordingly.

Designing Tables With Relationships in Mind

Whenever you create a new register, you should always ask yourself “how does this relate to what already exists?”

Imagine a Compliance department maintaining several separate registers:

Client Register

KYC Review Register

Legal Entity Register

Employee Register

Controlled Function Register

Employee Controlled Function Register (this is a slightly "special" table to store the assignment)

Risk Assessment Register

Why IDs Matter

If you look across these tables, you will notice the repeated use of identifiers, or IDs. In a relational database, the ID column is essential because it allows one table to link to another. This is what makes relationships possible. As I mentioned in a previous article, this column does not need to be called ID. But it does need to behave like a true primary key. In other words, it should:

• uniquely identify a record
• remain stable over time
• never be empty

An ID usually carries little or no business meaning on its own. Its purpose is not to describe the record in any way but to create a connection to another table, therefore to other pieces of information. For example, if you store an employee_id in a table, you do not need to repeat the employee’s first name and surname there as well. The ID is enough to link back to the Employee Register whenever you need the additional information.

That simple design choice has important consequences. It reduces duplication and helps prevent inconsistencies, errors, and data deterioration over time.

If we were to visualise how these tables connect to one another, it would look something like this (what is known as an Entity-Relationship (ER) diagram created with Mermaid):

The purpose of such a diagram is not to impress anyone with technical terminology. It is simply to show that what appear to be separate registers are often parts of the same database. Again: all data is linked.

You may hesitate at this point, thinking that you would have to create and maintain dozens of tables, foreign keys, relationships and that Excel feels simpler. In reality, relational design usually reduces workload (with dedicated tools).

A Word on Database Types

So far, I have referred specifically to

relational

databases, not just databases in general. This is because there are several types of databases., for instance, document databases, graph databases, and key-value stores, and other NoSQL databases. Each serves different use cases and comes with different strengths and limitations.

That said, for most Legal and Compliance use cases, relational databases remain the most relevant model at the moment. Whether you work in a law firm, a start-up, a regulated business, or a large corporate group, most structured Legal and Compliance data fits naturally into relational design. Registers, entities, employees, obligations, reviews, incidents, approvals, and policies all tend to have clear relationships that can be modelled well in tables.

For that reason, relational thinking is the framework I focus on here.

If You Want to Go Further

This article is not a full guide to database design, and it is not meant to be. But I strongly encourage Legal and Compliance professionals to learn the basics of relational database design, especially if you work in Compliance Operations, Legal Operations, or any role involving registers, reporting, or process improvement.

You do not need deep technical knowledge. You only need to understand the fundamentals: tables, keys, relationships, and normalisation.

There is a great deal of free material available online. Even one good introductory lecture can give you a much clearer mental model of how structured data works.

There is an eight-hour lecture available online that I personally found extremely valuable. Do not be put off by its length or by the slightly old-school format. The principles have not changed, and in a few hours you will gain a level of clarity that will stay with you throughout your career. You do not need to become technical. You only need to understand how structured data works.

The link is here.